
/**
 * 
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import metier.*;

import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Yves Boge
 *
 */
public class MatiereJDBC extends Matiere {

    //Attributs priv�s
    private final static String ID = "id_matiere"; //Nom de l'attribut idMatiere dans la BD
    private final static String UE = "UE"; //Nom de l'attribut ue en BD
    private final static String NOM = "nom_matiere"; //Nom de l'attribut nom en BD
    private final static String COEFFICIENT = "coefficient_divise"; //Nom de l'attribut coefficient en BD
    private final static String DESCRIPTION = "description_matiere"; //Nom de l'attribut description en BD
    private final static String NOMTABLE = "matiere";

    //Requete SQL permettant de mettre a jour l'objet dans la base de donnees
    private final static String updateQuery = "UPDATE "+ NOMTABLE +" SET " + UE + " = ?, " + NOM + " = '?', " + COEFFICIENT + " = '?', " + DESCRIPTION + " = ? where " + ID + " = ?";
    //Requete SQL permettant de recuperer la liste de toutes les salles de la base de donnees
    private final static String selectQuery = "SELECT * FROM "+ NOMTABLE;
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String loadQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + ID + " = ?";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String insertQuery = "INSERT INTO " + NOMTABLE + " VALUES(?, ?, ?, ?, ?)";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String deleteQuery = "DELETE FROM " + NOMTABLE + " WHERE " + ID + " = ?";
    private final static String idQuery = "SELECT seq_matiere.nextval  FROM dual";

    private final static String selectQuerySemestre = "select * from "
            + NOMTABLE + " mat, "
            + UEJDBC.getNomTable() + " u where mat."
            + UE + " = u."
            + UEJDBC.getIdCL() + " and u."
            + UEJDBC.getSemestreCL() + " = ?";

    //Requete SQL permettant de recuperer la liste de toutes les salles de la base de donnees
    private final static String selectSemestreQuery = "SELECT m.* FROM " + NOMTABLE + " m, " + UEJDBC.getNomTable() + " u " 
    				+ "WHERE m.ue = u.id_ue AND u.semestre = ?";
   
    
    static String getIdCL() {
        return ID;
    }

    static String getUECL() {
        return UE;
    }
    /* Attributs priv�s */
	private int id;
	/**
	 * @return the id id de l'UE
	 */
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    /**
     * @param id identifiant de la matiere
     */
    /* (non-Javadoc)
     * @see metier.Matiere#load(int)
     */
    public MatiereJDBC(UE ue, String nom, String description, int coefficient) {
        super(ue, nom, description, coefficient);
    }

    public MatiereJDBC(){
        super();
    }

    protected int createId() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next()) {
                throw new ExceptionMetier("creating new id failed due to non generated id");
            }
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }

    @Override
    public void load(int id) throws ExceptionMetier {
        try {
            setId(id);
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(loadQuery);
            psta.setInt(1, getId());
            ResultSet rset = psta.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (rset.getInt(ID) != getId()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            setNom(rset.getString(NOM));
            //UE ue = new UEJDBC();
            //ue.load(rset.getInt(UE));
            //setUe(ue);
            setDescription(rset.getString(DESCRIPTION));
            setCoefficient(rset.getInt(COEFFICIENT));
            rset.close();
            psta.close();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }

    }

    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement sta = con.prepareStatement(deleteQuery);
            sta.setInt(1, getId());
            sta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    @Override
    public void insert() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
			PreparedStatement insertEnseignant = con.prepareStatement(insertQuery);
                        if( getId() == 0) setId(createId());
			insertEnseignant.setInt(1, this.getId());
			//insertEnseignant.setInt(2, this.getUe().getId());
			insertEnseignant.setString(3, this.getNom());
                        insertEnseignant.setString(4, getDescription());
			insertEnseignant.setInt(5, this.getCoefficient());
			insertEnseignant.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
    }

    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement update = con.prepareStatement(updateQuery);
            update.setInt(1, getUe().getId());
            update.setString(2, getNom());
            update.setString(3, getDescription());
            update.setInt(4, getCoefficient());
            update.setInt(5, getId());
            update.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }
    /**
	 * Methode qui retourne la liste des matieres dans le systeme de stockage
	 * @return ArrayList<ElementListe> liste des matieres sous forme d'un ArrayList de ElementListe
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<ElementListe> select() throws ExceptionMetier {
		ArrayList<ElementListe> listeMatiere = new ArrayList<ElementListe>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    Statement s = con.createStatement();
		    res = s.executeQuery(selectQuery);
		
		    while (res.next())
			{
				//Ajoute la matiere a la liste
				listeMatiere.add(new ElementListe(res.getInt(ID), res.getString(NOM)));
			} 
		    return listeMatiere;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
	   /**
	 * Methode qui retourne la liste des matieres dans le systeme de stockage
	 * @return ArrayList<ElementListe> liste des matieres sous forme d'un ArrayList de ElementListe
	 * @throws ExceptionMetier 
	 */
	public static ArrayList<ElementListe> selectMatiereSemestre(int semestre) throws ExceptionMetier {
		ArrayList<ElementListe> listeMatiere = new ArrayList<ElementListe>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    PreparedStatement select = con.prepareStatement(selectSemestreQuery);
            select.setInt(1, semestre);
           
		    res = select.executeQuery();
		
		    while (res.next())
			{
				//Ajoute la matiere a la liste
				listeMatiere.add(new ElementListe(res.getInt(ID), res.getString(NOM)));
			} 
		    return listeMatiere;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
        
        public static ArrayList<ElementListe> selectSemestre(int idSemestre) throws ExceptionMetier {
        try {
            ArrayList<ElementListe> list = new ArrayList<ElementListe>();
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(selectQuerySemestre);
            psta.setInt(1, idSemestre);
            ResultSet rset = psta.executeQuery();
            while (rset.next()) {
                list.add(new ElementListe(rset.getInt(ID), rset.getString(NOM)));
            }
            rset.close();
            psta.close();
            return list;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }



    public static String getNomTable() {
        return NOMTABLE;
    }


}
